package day11;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import javax.swing.text.AbstractDocument.BranchElement;


public class StudentManager {
	private static ArrayList<Student> list = null;
	private static Scanner scanner = new Scanner(System.in);

	public static void main(String[] args) {
		// 从数据库中查询所有的学生
		list = selectAll();
		
		while (true) {
			System.out.println("1、打印所有学生：1");
			System.out.println("2、添加学生：2");
			System.out.println("3、删除学生：3");
			System.out.println("4、查找学生(根据名字模糊查找)：4");
			System.out.println("5、修改学生（这个在控制台里面比较麻烦，有能力同学可以做一下）：5");
			System.out.println("0、退出：0");
			int type = scanner.nextInt();
			if (type == 0) {
				System.out.println("退出");
				break;
			}
			
			switch (type) {
			case 1:// 打印所有学生
				printAllStudent();
				break;
			case 2:// 添加学生
				insertStudent();
				break;
			case 3: //删除学生
				deleteStudent();
				
				break;
			case 4: //查找学生
				selectStudent();
				break;
			case 5: //修改学生
				updateStudent();
				break;
			default:
				break;
			}
			
			
		}
	}

	//修改学生
	private static void updateStudent() {
		// TODO Auto-generated method stub
		System.out.println("请输入待修改的学生的 姓名：");
		String nameOld = scanner.next();
		System.out.println("请输入新名字：");
		String nameNew = scanner.next();
		System.out.println("请输入新年龄：");
		int age = scanner.nextInt();
		System.out.println("请输入新性别：");
		String gender = scanner.next();
		// 首先添加到数据库中
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE student SET `name` = ? , age = ? , gender = ? WHERE `name`= ?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, nameNew);
			preparedStatement.setInt(2, age);
			preparedStatement.setString(3, gender);
			preparedStatement.setString(4, nameOld);
			
			int count = preparedStatement.executeUpdate();
			if (count == 1) {
				System.out.println("修改成功");
			} else {
				System.out.println("修改失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		// 数据已经插入到数据库，但是内存ArrayList list中的数据没有添加上新添加的这个
		// 这里有两种处理方式：
		// 1、jdbc在插入Student的时候可以返回那个自增的id，大家可以去搜索一下
		// 2、重新去数据可查询一下，虽然麻烦但是我们已经封装成一个方法了，可以直接调用
		list = selectAll();
	}

	
	
	//查找学生
	private static void selectStudent() {
		// TODO Auto-generated method stub
		System.out.println("-----------------------");
		System.out.println("请输入名字：");
		String name = scanner.next();
		
		ArrayList<Student> listSelect = new ArrayList<Student>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,gender from student where `name` = ?";
			// 得到prepareStatement而且把sql语句传过去
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				Student student = new Student(id, name, age, gender);
				listSelect.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		//打印这个查询的结果
		for (Student student : listSelect) {
			System.out.println(student);
		}
		System.out.println("-----------------------");			
	}

	
	
	
	//删除学生
	private static void deleteStudent() {
		System.out.println("-----------------------");
		System.out.println("请输入名字：");
		String name =  scanner.next();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "Delete FROM student WHERE `name` = ?"; 
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			int count = preparedStatement.executeUpdate();
			if (count == 1) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		
		System.out.println("-----------------------");
		list = selectAll();
		
	}
	

	//打印学生
	private static void printAllStudent() {
		System.out.println("-----------------------");
		for (Student student : list) {
			System.out.println(student);
		}
		System.out.println("-----------------------");
	}

	//添加学生
	private static void insertStudent() {
		System.out.println("请输入名字：");
		String name = scanner.next();
		System.out.println("请输入年龄：");
		int age = scanner.nextInt();
		System.out.println("请输入性别：");
		String gender = scanner.next();
		
		// 首先添加到数据库中
		Connection connection = null;
		//
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(`name`,age,gender) VALUES(?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, age);
			preparedStatement.setString(3, gender);
			int count = preparedStatement.executeUpdate();
			if (count == 1) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		// 数据已经插入到数据库，但是内存ArrayList list中的数据没有添加上新添加的这个
		// 这里有两种处理方式：
		// 1、jdbc在插入Student的时候可以返回那个自增的id，大家可以去搜索一下
		// 2、重新去数据可查询一下，虽然麻烦但是我们已经封装成一个方法了，可以直接调用
		list = selectAll();
	}

	//从数据库中查询所有的学生
	private static ArrayList<Student> selectAll() {
		ArrayList<Student> list = new ArrayList<Student>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,gender from student";
			// 得到prepareStatement而且把sql语句传过去
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}
}
